package control;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import model.Shetuanjieshao;
import util.Pagination;
import db.DBConnect;

public class ShetuanjieshaoControl {
	public static Pagination pageInstance = null;
	
	/**
	 * 增加
	 * @author 
	 * @return
	 * 2012-03-18
	 */
	public boolean insertShetuanjieshao(Shetuanjieshao shetuanjieshao)
	{
		DBConnect dbc = new DBConnect();
		String sql ="insert into shetuanjiehao (shetuanName,shetuanLeader,shetuanNeirong,shetuanShuxing,darenName) values (?,?,?,?,?)";
		
		dbc.prepareStatement(sql);
		
		try {
			dbc.setString(1, shetuanjieshao.getShetuanName());
			dbc.setString(2, shetuanjieshao.getShetuanLeader());
			dbc.setString(3, shetuanjieshao.getShetuanNeirong());
			dbc.setString(4, shetuanjieshao.getShetuanShuxing());
			dbc.setString(4, shetuanjieshao.getDarenName());
					
			dbc.executeUpdate();
			return true;
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		}finally {
			dbc.close();
		}
	}
	
	/*判断数据库里面是否存在用户*/
	public static boolean isShetuanjieshao(String a,String b)
	{
		DBConnect dbc = new DBConnect();
		String sql ="select * from shetuanjieshao where shetuanname = ? and shetuanLeader=? and shetuanNeirong=? and shetuanShuxing=?";
		dbc.prepareStatement(sql);
		try { 
			dbc.setString(1,a);
			dbc.setString(2,b);		
			ResultSet rs= dbc.executeQuery();
			if(rs.next()){
				return true;
			}
			else{
				return false;
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			dbc.close();
		}return false;
	}
	
	
	/**
	 * 根据id获取信息
	 * @author dujuan
	 * 2012-02-23
	 */
	
	public Shetuanjieshao GetShetuanjieshao(int id)
	{
		DBConnect dbc = new DBConnect();
		String sql = "select * from shetuanjieshao where shetuanId = ?";

		dbc.prepareStatement(sql);
		
		try {
			dbc.setInt(1, id);
			ResultSet rs = dbc.executeQuery();
			Shetuanjieshao shetuanjieshao = new Shetuanjieshao();
			
			if(rs.next())
			{
				shetuanjieshao.setShetuanName(rs.getString("shetuanName"));
				shetuanjieshao.setShetuanLeader(rs.getString("ShetuanLeader"));
				shetuanjieshao.setShetuanNeirong(rs.getString("ShetuanNeirong"));
				shetuanjieshao.setShetuanShuxing(rs.getString("ShetuanShuxing"));
				shetuanjieshao.setShetuanShuxing(rs.getString("DarenName"));

				return shetuanjieshao;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			dbc.close();
		}
		return null;
	}
	
	
	/**
	 * 删除
	 * @author dujuan
	 * 2012-02-23
	 */
	
	public boolean deleteShetuanjieshao(int id)
	{
		DBConnect dbc = new DBConnect();
		String sql = "delete from shetuanjieshao where shetuanId =?";
		
		dbc.prepareStatement(sql);
		
		try {
			dbc.setInt(1, id);
			dbc.executeUpdate();
			return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		}finally{
			dbc.close();
		}
		
	}
	
	/**
	 * 修改
	 * @author dujuan
	 * 2012-02-23
	 */
	
	public boolean updateShetuanjieshao (Shetuanjieshao shetuanjieshao, int id)
	{
		DBConnect dbc = new DBConnect();
		String sql = "update shetuanjieshao set shetuanName=? ,shetuanLeader=?,shetuanNeirong=?,shetuanShuxing=?,darenName=?  where shetuanId =?";
		dbc.prepareStatement(sql);
		
		try {
			dbc.setString(1, shetuanjieshao.getShetuanName());
			dbc.setString(2, shetuanjieshao.getShetuanLeader());
			dbc.setString(3, shetuanjieshao.getShetuanNeirong());
			dbc.setString(4, shetuanjieshao.getShetuanShuxing());
			dbc.setString(5, shetuanjieshao.getDarenName());
			dbc.setInt(6, id);
			
			//System.out.println("~~~: " + id);
			dbc.executeUpdate();
			return true;
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		}finally{
			dbc.close();
		}
	}
	
	
	/**
	 * 列表返回显示总结
	 * 
	 */
	
	public Collection GetShetuanjieshao()
	{
		DBConnect dbc = new DBConnect();
		String sql = "select * from shetuanjieshao order by shetuanId desc";
		dbc.prepareStatement(sql);
		Collection<Shetuanjieshao> shetuanjieshaoList = new ArrayList<Shetuanjieshao>();
		try {
			ResultSet rs = dbc.executeQuery();
			while(rs.next())
			{
				
				Shetuanjieshao shetuanjieshao = new Shetuanjieshao();
				shetuanjieshao.setShetuanId(rs.getInt("shetuanId"));
				shetuanjieshao.setShetuanName(rs.getString("shetuanName"));
				shetuanjieshao.setShetuanLeader(rs.getString("shetuanLeader"));
				shetuanjieshao.setShetuanNeirong(rs.getString("shetuanNeirong"));
				shetuanjieshao.setShetuanShuxing(rs.getString("shetuanShuxing"));
				shetuanjieshao.setShetuanShuxing(rs.getString("shetuanDarenName"));
			}
			return shetuanjieshaoList;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			dbc.close();
		}
		
		return null;
	}
	


	/*
	 * 返回总结列表，分页显示
	 */
	
	public Collection GetShetuanjieshao(int page, int rowsPerPage)
	{
		String sql = "select * from shetuanjieshao order by shetuanId desc";
		pageInstance = new ShetuanjieshaoControlPage(sql,rowsPerPage);
		
		try {
			return pageInstance.getPage(page);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return null;

	}
	

class ShetuanjieshaoControlPage extends Pagination{
	public ShetuanjieshaoControlPage(String sql, int rowsPerPage)
	{
		super(sql,rowsPerPage,1);
	}
	
	@Override
	protected Collection<Shetuanjieshao> packResultSet(ResultSet rs) throws SQLException {
		
		Collection<Shetuanjieshao> shetuanjieshaoList = new ArrayList<Shetuanjieshao>();
		while(rs.next())
		{
			Shetuanjieshao shetuanjieshao = new Shetuanjieshao();
			
			shetuanjieshao.setShetuanId(rs.getInt("shetuanId"));	
			shetuanjieshao.setShetuanName(rs.getString("shetuanName"));
			shetuanjieshao.setShetuanLeader(rs.getString("shetuanLeader"));
			shetuanjieshao.setShetuanNeirong(rs.getString("shetuanNeirong"));
			shetuanjieshao.setShetuanShuxing(rs.getString("shetuanShuxing"));
			shetuanjieshao.setShetuanShuxing(rs.getString("DarenName"));
					
			shetuanjieshaoList.add(shetuanjieshao);
			shetuanjieshao = null;
			}
			return shetuanjieshaoList;
			
		}
	
	}
	
}
	


	
	
	

	

	
	
